<?php
//统计中心
namespace app\admin\controller;

use app\admin\controller\Base;
use app\common\model\SscCaipailist;
use app\common\model\SscKjjglist;
use app\common\model\SsctouzhuListno;
use app\common\model\SsctouzhuListok;
use app\common\model\SscPeilv;
use app\common\model\SscChongzhiList;
use app\common\model\SscTixianList;
use app\common\model\SscUsername;
use think\Paginator;
use think\Db;
use app\common\model\SystemSet;
use app\admin\model\TouzhuListOk;
use app\common\model\HuiYuanSupervise;
class Statistics extends Base
{
    protected $ssccaipailist=[];
    protected $ssckjjglist=[];
    protected $ssctouzhulistno=[];
    protected $ssctouzhulistok=[];
    protected $sscpeilv=[];
    protected $sscchongzhilist=[];
    protected $ssctixianlist=[];
    protected $sscusername=[];
    protected $sysment;
    Public function _initialize()
	{
		parent::_initialize();
        $this->ssccaipailist = new SscCaipailist();
        $this->ssckjjglist = new SscKjjglist();
        $this->ssctouzhulistno = new SsctouzhuListno();
        $this->ssctouzhulistok = new SsctouzhuListok();
        $this->sscpeilv = new SscPeilv();
        $this->sscchongzhilist = new SscChongzhiList();
        $this->ssctixianlist = new SscTixianList();
        $this->sscusername = new SscUsername();
        $this->sysment = new SystemSet();
        $this->member_model= new HuiYuanSupervise();
	}
    /**
     **统计中心彩种列表
     **/
    public function tj_czlist()
    {
        $czlisu=input('czlisu') ? input('czlisu') : 1;

        $fileds="id,cp_name_old,cp_name_new,cp_fp_time,cp_mb_px,cp_dn_px,cp_mb_index,cp_dn_index,cp_if_open,cpclass";
        $where['cp_if_open']=1;
        $order='';
        $limit='';
        $CZLISU=$this->ssccaipailist->TongJi($fileds,$where,$order,$limit);

        $fileds="id,cp_name_old,cp_name_new,cp_fp_time,cp_mb_px,cp_dn_px,cp_mb_index,cp_dn_index,cp_if_open,cpclass";
        $where['id']=$czlisu;
        $order='';
        $CZNAME_CX=$this->ssccaipailist->TongJi($fileds,$where,$order);

        $fileds="id,czid,qihao,jztime,kjjg,iftz,ps";
        $where = array(
            'czid'=>$czlisu,
        );
        $order='';
        $limit='';
        $fileds="count(id,czid,qihao,jztime,kjjg,iftz,ps)";
        $where['czid']=$czlisu;
        $order='id desc';
        $limit='15';
        $KJLIST=$this->ssckjjglist->JiLu($fileds,$where,$order,$limit);

        $QISHU=$KJLIST[0]['qihao'];

        if(input('czlisu')){

            $canshu="?id=".input('id')."&cid=".input('cid');
            $listareas=Db::name('kjjg_list')->where(array('czid'=>$czlisu))->order('qihao','DESC')->paginate(15,false,array('query'=>array('czlisu'=>$czlisu,'canshu'=>$canshu)))->each(function($item,$czlisu){
                $item['kjjg'] = kjjg_or_img($item['kjjg'],$item['czid']);
                return $item;
            });
            $fileds="*";
            $order='id desc';
            $CZNAME_CX=$this->ssccaipailist->TongJi($fileds,array('id'=>$czlisu),$order);

            //分页显示输出
            $page=$listareas->render();
            $this->assign('listarea',$listareas);
            $this->assign('CZID',$czlisu);

        }else{
            $canshu="?id=".input('id')."&cid=".input('cid');
            //分页数据集
            $listarea=Db::name('kjjg_list')->where(array('czid'=>$czlisu))->order('qihao','DESC')->paginate(15,false,array('query'=>array('czlisu'=>$czlisu,'canshu'=>$canshu)))->each(function($item,$czlisu){

                $item['kjjg'] = kjjg_or_img($item['kjjg'],$item['czid']);
                return $item;
            });
            //分页显示输出
            $this->assign('listarea',$listarea);
            $this->assign('CZID',$czlisu);
        }

        if($CZNAME_CX[0]['cp_name_new']==""){$CZNAME=$CZNAME_CX[0]['cp_name_old'];}else{$CZNAME=$CZNAME_CX[0]['cp_name_new'];}


        //模板赋值
        $BENQISHUJU=$this->CAIPIAO_TZ_ZJ_ZS($czlisu,$QISHU);
        $this->assign('CZNAME',$CZNAME);
        $this->assign('BENQISHUJU',$BENQISHUJU);
        $this->assign('KJLIST',$KJLIST);

        $this->assign('czlisu',$CZLISU);
        return $this->fetch();
    }
    //彩票每一期投注金额，中奖金额，中奖注数 //注单数量
    public function  CAIPIAO_TZ_ZJ_ZS($czlisu,$QISHU){
        $fileds="id,czid,qihao,jztime,kjjg,iftz,ps";
        $where=array(
            'czid'=>$czlisu,
            'qihao'=>$QISHU,
        );
        $order='';
        $limit='';
        $QUKJJG=$this->ssckjjglist->JiLu($fileds,$where,$order,$limit);
        if($QUKJJG[0]['kjjg']<1){
            $fileds="*";
            $where=array(
                'czid'=>$czlisu,
                'qihao'=>$QISHU,
            );
            $order='';
            $limit='';
            $QTZZD=$this->ssctouzhulistno->TouZhu($fileds,$where,$order,$limit);
        }else{
            $fileds="*";
            $where=array(
                'czid'=>$czlisu,
                'qihao'=>$QISHU,
            );
            $order='';
            $limit='';
            $QTZZD=$this->ssctouzhulistok->TouZhuSuccess($fileds,$where,$order,$limit);
        }

        $RETUXX=array();

        $RETUXX[0]=0; //投注金额

        $RETUXX[1]=0;  //中奖金额

        $RETUXX[2]=0;  //中奖注数

        $RETUXX[3]=0;  //注单数量

        foreach($QTZZD as $TZ){

            $RETUXX[3]=$RETUXX[3]+1;

            $RETUXX[0]=$RETUXX[0]+$TZ['tzje'];

            $RETUXX[1]=$RETUXX[1]+$TZ['zjje'];

            $RETUXX[2]=$RETUXX[2]+$TZ['zjzs'];

        }

        return $RETUXX;

    }
    /**
    **六合彩显示
     **/
    public function tj_lhclist(){
        $qihao = input('qihao') ? input('qihao') : '';
        $total_tzje = 0;
        $total_zjje = 0;
        
        $issue = Db::name('kjjg_list')->where('czid',9)->order('qihao','DESC')->limit(20)->column('qihao');
        if($qihao) { 
            $qishulist = Db::name('touzhu_list_ok')->alias('tlo')
            ->field('tlo.tzhm,SUM(tlo.tzje) as sum_tzje,SUM(tlo.zjje) as sum_zjje')
            ->join('__PLAY_CLASSIFY__ pc','tlo.wfid=pc.id')
            ->where(['tlo.czid'=>9,'tlo.qihao'=>$qihao,'pc.id'=>164])
            ->group('tlo.tzhm')
            ->order('tlo.tzhm','ASC')
            ->select();

        } else {
            $qishulist = Db::name('touzhu_list_ok')->alias('tlo')
            ->field('tlo.tzhm,SUM(tlo.tzje) as sum_tzje,SUM(tlo.zjje) as sum_zjje')
            ->join('__PLAY_CLASSIFY__ pc','tlo.wfid=pc.id')
            ->where(['tlo.czid'=>9,'pc.id'=>164])
            ->group('tlo.tzhm')
            ->order('tlo.tzhm','ASC')
            ->select();
        }

        foreach($qishulist as $v) {
            $total_tzje += $v['sum_tzje'];
            $total_zjje += $v['sum_zjje'];
        }

        $this->assign('qihao',$qihao);
        $this->assign('issue',$issue);
        $this->assign('qishulist',$qishulist);
        $this->assign('total_tzje',$total_tzje);
        $this->assign('total_zjje',$total_zjje);

        return $this->fetch();
    }

    /**
     **平台报表
     **/
    public function tj_ptbj(){
    	
    	$time = [date("m-d",strtotime('-6 day')),date("m-d",strtotime('-5 day')),date("m-d",strtotime('-4 day')),date("m-d",strtotime('-3 day')),date("m-d",strtotime('-2 day')),date("m-d",strtotime('-1 day')),date("m-d")];
    	$today = array('chongzhi'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    					'xianshang_cz'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    					'xianxia_cz'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    					'rengong_cz'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
		    			'tixian'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    					'member_tx'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    					'rengong_tx'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
		    			'caijin'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    					'fanshui'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    					'today_yk'=>0
    					);
    	$member_people = array('number_people'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
			    			'add_chongzhi'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
			    			'add_dl'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
			    			'add_huiyuan'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
			    			'add_swmember'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0]);
    	$agent_analysis = array('agent_yk'=>0,
    							'agent_add'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    							'member_add'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    							'member_deposit'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    							'member_withdraw'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0],
    							'yj_fafang'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>0,'6'=>0]
    						);
		$lottery_category = Db::name('caipai_list')->field('id,cp_name_old')->select();
		$yk = $rd = array();
		foreach($lottery_category as $k=>$v){
			$yk[$v['id']] = array('name'=>$v['cp_name_old'],'value'=>['0'=>0,'1'=>0,'2'=>0,'3'=>0,'4'=>0,'5'=>'0','6'=>0],'color'=>rand(1111, 888888));
			$rd[$v['id']] = array('name'=>$v['cp_name_old'],'value'=>0,'color'=>rand(1111, 888888));
		}
		$lottery_analysis = array(
			'today_yk' => 0,
			'today_zs' => 0,
			'today_ze' => 0,
			'today_pj' => 0,
			'today_tz' => 0,
			'yk' => $yk,
			'tz' => $rd,
			'zj' => $rd,
			'rs' => $rd,
			'zs' => $rd
		);

		$lottery_user = array();
		$lottery_classification_user = array();
		$user = Db::name('username')->where('nbzh',1)->column('username');
		$users = implode(',', $user);
		foreach ($time as $k=>$ti){
    		$start = strtotime(date('Y').'-'.$ti.'00:00:00');
    		$end = strtotime(date('Y').'-'.$ti."23:59:59");
    		$today_chongzhi = Db::name('chongzhi_list')
					    		->field('id,username,cztime,je,czclass')
					    		->where(['zt'=>1,'czadmin'=>['<>','']])
					    		->whereTime('cztime',[$start,$end])
					    		->where('username','not in',$users)
					    		->select();
    		$today_rengong = Db::name('sgkk_list')
				    		->whereTime('kktime',[$start,$end])
				    		->where('username','not in',$users)
				    		->value('SUM(kkje)');
    		$today_tixian = Db::name('tixian_list')
				    		->where(['zt'=>1,'shadmin'=>['<>',''],'nbzh'=>['<>',1],'czadmin'=>['<>','']])
				    		->whereTime('shtime',[$start,$end])
				    		->value('SUM(txje)');
    		$today_fanshui = Db::name('fs_list')
				    		->whereTime('distribute_time',[$start,$end])
				    		->where('username','not in',$users)
				    		->value('SUM(fsje)');
    		$today_caijin = Db::name('caijin_list')
				    		->whereTime('fstime',[$start,$end])
				    		->where('username','not in',$users)
				    		->value('SUM(cjje)');
    		foreach ($today_chongzhi as $item){
    			$today['chongzhi'][$k] += empty($item['je']) ? 0 : $item['je'];
    			if($item['czclass']==1){
    				$today['xianshang_cz'][$k] += $item['je'];
    			}
    			if($item['czclass']==2){
    				$today['xianxia_cz'][$k] += $item['je'];
    			}
    			if($item['czclass']==3){
    				$today['rengong_cz'][$k] += $item['je'];
    			}
    		}
    	 	if($today['xianshang_cz'][$k]==''){$today['xianshang_cz'][$k] = 0;}
    		if($today['xianxia_cz'][$k]==''){$today['xianxia_cz'][$k] = 0;}
    		if($today['rengong_cz'][$k]==''){$today['rengong_cz'][$k] = 0;} 
    		$today['tixian'][$k] = $today_rengong+$today_tixian;
    		$today['member_tx'][$k] = empty($today_tixian) ? 0 : $today_tixian;
    		$today['rengong_tx'][$k] = empty($today_rengong) ? 0 : $today_rengong;
    		$today['fanshui'][$k] = empty($today_fanshui) ? 0 : $today_fanshui;
    		$today['caijin'][$k] = empty($today_caijin) ? 0 : $today_caijin;
    		
    		//会员分析
    		
    		$member = Db::name('username')
			    		->field('id,swzh,dl,tjr')
			    		->whereTime('reg_time',[$start,$end])
			    		->select();
    		
    		foreach ($member as $it){
    			if($it['swzh']!=1){
    				$member_people['number_people'][$k] += 1;
    			}
    			if($it['swzh']==1){
    				$member_people['add_swmember'][$k] += 1;
    			}
    			
    				$member_people['add_dl'][$k] += 1;
    				$agent_analysis['agent_add'][$k] += 1;
    			
    			if($it['swzh'] != '1'){
    				$member_people['add_huiyuan'][$k] += 1;
    			}
    			if($it['swzh'] != '1' && $it['tjr'] != ''){
    				$agent_analysis['member_add'][$k] += 1;
    			}
    		}
    		
    		$arr = array();
    		foreach ($today_chongzhi as $key=>$ite){
    			$arr[] = $ite['username'];
    		}
    		$member_people['add_chongzhi'][$k] = count(array_unique($arr));
    		
    		//代理分析
    		$money_chongzhi = Db::name('chongzhi_list')
    						->alias('a')
    						->join('__USERNAME__ b','b.username=a.username')
    						->whereTime('b.reg_time',[$start,$end])
    						->where(['a.zt'=>1,'a.czadmin'=>['<>','']])
    						->where('b.nbzh','<>',1)
    						->value('SUM(a.je)');
    		$member_tixian = Db::name('tixian_list')
    						->alias('a')
    						->join('__USERNAME__ b','b.username=a.username')
    						->whereTime('b.reg_time',[$start,$end])
    						->where(['a.zt'=>1,'a.shadmin'=>['<>',''],'a.nbzh'=>['<>',1],'a.czadmin'=>['<>','']])
    						->value('SUM(a.txje)');
    		$member_rengong = Db::name('caijin_list')
					    		->alias('a')
					    		->join('__USERNAME__ b','b.username=a.username')
					    		->whereTime('b.reg_time',[$start,$end])
					    		->where('b.nbzh','<>',1)
					    		->value('SUM(a.cjje)');
    		$agent = Db::name('agent_balance')->whereTime('add_time',[$start,$end])->value('SUM(balance)');
    		$agent_analysis['yj_fafang'][$k] = empty($agent) ? 0 : $agent;
    		$agent_analysis['member_deposit'][$k]=$money_chongzhi?$money_chongzhi:0;
    		$agent_analysis['member_withdraw'][$k]=(empty($member_tixian)? 0 : $member_tixian) + (empty($member_rengong) ? 0 :$member_rengong);

			/**
			 * 投注分析
			 */
			$today_touzhu = Db::name('touzhu_list_ok')
							->alias('a')
							->join('__CAIPAI_LIST__ b','b.id=a.czid','left')
							->field('a.*,b.cp_name_old')
							->whereTime('tz_time',[$start,$end])->select();

			foreach ($today_touzhu as $item){
				if( floatval($item['zjje']) > 0 ){
					$diff = floatval($item['zjje'])-floatval($item['tzje']);
					$lottery_analysis['yk'][$item['czid']]['value'][$k] += $diff;
				}else{
					$lottery_analysis['yk'][$item['czid']]['value'][$k] -= floatval($item['tzje']);
				}
				//当天信息
				if( 6==intval($k) ){
					if($item['cp_name_old']==$lottery_analysis['zs'][$item['czid']]['name']){
						$lottery_analysis['zs'][$item['czid']]['value']++;
					}

					if( floatval($item['zjje']) > 0 ){
						$diff = floatval($item['tzje'])-floatval($item['zjje']);
						$lottery_analysis['today_yk'] += $diff;
					}else{
						$lottery_analysis['today_yk'] += floatval($item['tzje']);
					}
					
					$lottery_analysis['today_zs']++;
					$lottery_analysis['today_ze'] += floatval($item['tzje']);
					$lottery_analysis['today_pj'] += floatval($item['zjje']);
					if( !in_array($item['username'],$lottery_user) ) {
						$lottery_user[] = $item['username'];
						$lottery_analysis['today_tz']++;
					}
					$lottery_analysis['tz'][$item['czid']]['value'] += floatval($item['tzje']);
					$lottery_analysis['zj'][$item['czid']]['value'] += floatval($item['zjje']);
					if( !in_array($item['czid'].'=='.$item['username'],$lottery_classification_user) ){
						$lottery_analysis['rs'][$item['czid']]['value']++;
						$lottery_classification_user[] = $item['czid'].'=='.$item['username'];
					}
				}
			}
			$lottery_analysis['today_yk'] = sprintf("%.2f",$lottery_analysis['today_yk']);
    	}
    	$today['today_yk'] = $today['chongzhi'][6]-$today['tixian'][6];
		$agent_analysis['agent_yk'] = $agent_analysis['member_deposit'][6]-$agent_analysis['member_withdraw'][6];
    	$this->assign('today',$today);
    	$this->assign('member_people',$member_people);
    	$this->assign('agent_analysis',$agent_analysis);
    	$this->assign('lottery_analysis',$lottery_analysis);
    	$this->assign('time',$time);
 
    	return $this->fetch();
    }
   
    public function qpcz(){
    	if(input('opentime') != null){
	    	$star = input('opentime');
	    	$end = input('viretime');
	    	$star_time = strtotime(input('opentime')."00:00:00");
	    	$end_time = strtotime(input('viretime')."23:59:59");
	    	$sjc = $end_time-$star_time;
	    	if($sjc>1296000){
	    		$this->sysment->alertWin('查看时间必须小于15天，或者选取其他方式查看。',url('Statistics/qpcz'));
	    	}
	    	if($star_time > $end_time){
	    		$this->sysment->alertWin('开始天数必须小于结束天数',url('Statistics/qpcz'));
	    	}
	    	$date = $this->dates_range($star, $end);
    	}else{
    		$date = [];
    		for($i=15;$i>=1;$i--){
    			$date[] = date('Y-m-d',mktime(0, 0, 0, date("m")  , date("d")-$i));
    		}
    	}
    	$arr = array();
    	foreach ($date as $k=>$v){
    		$starttime = strtotime($v.'00:00:00');
    		$endtime = strtotime($v.'23:59:59');
    		$where['cztime']=['between',$starttime.','.$endtime];
    		$czzje = 0;
    		$je = Db::name('chongzhi_list')->where($where)->select();
    		foreach ($je as $value){
    			$czzje += $value['je'];
    		}
    		$arr[]=$czzje;
    	}

    	$this->assign('date',$date);
    	$this->assign('arr',$arr);

    	$this->assign('str',date('Y-m-d'));
    	return $this->fetch();
    }
 	public function zsj(){
 		$arr = array();
 		for ($i=1;$i<8;$i++){
			$arr[$i]['star_week'] = date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m"),date("d")-date("w")+1-7*$i,date("Y")));
			$arr[$i]['end_week'] = date("Y-m-d H:i:s",mktime(23,59,59,date("m"),date("d")-date("w")+7-7*$i,date("Y")));
 		}
 		$cz = array();
 		foreach ($arr as $v){
 			$start = strtotime($v['star_week']);
 			$end = strtotime($v['end_week']);
 			$where['cztime'] = ['between',$start.','.$end];
 			$czzje = 0;
 			$je = Db::name('chongzhi_list')->where($where)->select();
 			foreach ($je as $value){
 				$czzje += $value['je'];
 			}
 			$cz[] = $czzje;
 			
 		}
 		$this->assign('cz',$cz);
 		return $this->fetch();
 	}
 	public function ysj(){
 		$arr = array();
 		$min = array();
 		for ($i=1;$i<13;$i++){
	 		$arr[$i]['star_min']= date("Y-m-d H:i:s",mktime(0, 0 , 0,date("m")-$i,1,date("Y")));
	 		$arr[$i]['end_min'] = date("Y-m-d H:i:s",mktime(23,59,59,date("m")-$i+1 ,0,date("Y")));
	 		$min[]= date("Y-m",mktime(0, 0 , 0,date("m")-$i,1,date("Y")));
 		}
 		$cz = array();
 		foreach ($arr as $v){
 			$start = strtotime($v['star_min']);
 			$end = strtotime($v['end_min']);
 			$where['cztime'] = ['between',$start.','.$end];
 			$czzje = 0;
 			$je = Db::name('chongzhi_list')->where($where)->select();
 			foreach ($je as $value){
 				$czzje += $value['je'];
 			}  
 			$cz[] = $czzje;
 		}
 		$this->assign('min',$min);
 		$this->assign('cz',$cz);
 		return $this->fetch();
 	}
    function dates_range($date1, $date2)
    {
    	if ($date1<$date2)
    	{
    		$dates_range[]=$date1;
    		$date1=strtotime($date1);
    		$date2=strtotime($date2);
    		while ($date1!=$date2)
    		{
    			$date1=mktime(0, 0, 0, date("m", $date1), date("d", $date1)+1, date("Y", $date1));
    			$dates_range[]=date('Y-m-d', $date1);
    		}
    	}
    	return $dates_range;
    }
    //财务统计
	public function financialStatistics() {

		$post = input('post.');
		$search_time = isset($post['searchtime']) ? $post['searchtime']: date('Y-m-d',time());
		$log = Db::name('statistics_financial')->where(['search_time'=>$search_time])->select();
		$ret = [];

		if( count($log) > 0 ){
			foreach( $log as $k=>$v ){
				$ret[$v['tag']] = array(
					'day_je' => $v['day_je'],
					'day_count' => $v['day_count'],
					'day_user' => $v['day_user'],
					'month_je' => $v['month_je'],
					'month_count' => $v['month_count'],
					'month_user' => $v['month_user']
				);
			}
		}else{
			$day_begin = strtotime( $search_time.' 00:00:00' );
			$day_end = strtotime( $search_time.' 23:59:59' );

			$search_month = date('Y-m',strtotime($search_time));
			$search_month_day = date('t',strtotime($search_time));

			$month_start = strtotime( $search_month.'-01 00:00:00' );
			$month_end = strtotime( $search_month.'-'.$search_month_day.' 23:59:59' );

			$day_time=[$day_begin,$day_end];
			$month_time=[$month_start,$month_end];
			$user = Db::name('username')->where('nbzh',1)->column('username');
			$users = implode(',', $user);
			/**
			 * 线下存款
			 */
			$xxck_day = Db::name('chongzhi_list')->field('SUM(je) as day_je,COUNT(je) as day_count, COUNT(DISTINCT username) as day_user')->where(['zt'=>1,'czclass'=>1])->where('username','not in',$users)->whereTime('cztime','BETWEEN',$day_time)->find();
			$xxck_month = Db::name('chongzhi_list')->field('SUM(je) as month_je,COUNT(je) as month_count, COUNT(DISTINCT username) as month_user')->where(['zt'=>1,'czclass'=>1])->where('username','not in',$users)->whereTime('cztime','BETWEEN',$month_time)->find();
			$ret['xx'] = array_merge($xxck_day,$xxck_month);
			/**
			 * 线上存款
			 */
			$xsck_day = Db::name('chongzhi_list')->field('SUM(je) as day_je,COUNT(je) as day_count, COUNT(DISTINCT username) as day_user')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>2])->whereTime('cztime','BETWEEN',$day_time)->find();
			$xsck_month = Db::name('chongzhi_list')->field('SUM(je) as month_je,COUNT(je) as month_count, COUNT(DISTINCT username) as month_user')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>2])->whereTime('cztime','BETWEEN',$month_time)->find();
			$ret['xs'] = array_merge($xsck_day,$xsck_month);
			/**
			 * 人工存入
			 */
			$rgcr_day = Db::name('chongzhi_list')->field('SUM(je) as day_je,COUNT(je) as day_count, COUNT(DISTINCT username) as day_user')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$day_time)->find();
			$rgcr_month = Db::name('chongzhi_list')->field('SUM(je) as month_je,COUNT(je) as month_count, COUNT(DISTINCT username) as month_user')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$month_time)->find();
			$ret['rgcr'] = array_merge($rgcr_day,$rgcr_month);
			/**
			 * 存款统计
			 */
			$rk_day_user = Db::field('username')
					->table('kkty_chongzhi_list')
					->group("username")->where(['zt'=>1,'czclass'=>1])->where('username','not in',$users)->whereTime('cztime','BETWEEN',$day_time)
					->union(function($query) use ($day_time,$users){
						$query->field('username')->table('kkty_chongzhi_list')->where('username','not in',$users)->group("username")->where(['zt'=>1,'czclass'=>2])->whereTime('cztime','BETWEEN',$day_time);
					})->union(function($query) use ($day_time,$users){
						$query->field('username')->table('kkty_chongzhi_list')->where('username','not in',$users)->group("username")->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$day_time);
					})->select();
			$rk_month_user = Db::field('username')
					->table('kkty_chongzhi_list')
					->group("username")->where(['zt'=>1,'czclass'=>1])->where('username','not in',$users)->whereTime('cztime','BETWEEN',$month_time)
					->union(function($query) use ($month_time,$users){
						$query->field('username')->table('kkty_chongzhi_list')->where('username','not in',$users)->group("username")->where(['zt'=>1,'czclass'=>2])->whereTime('cztime','BETWEEN',$month_time);
					})->union(function($query) use ($month_time,$users){
						$query->field('username')->table('kkty_chongzhi_list')->where('username','not in',$users)->group("username")->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$month_time);
					})->select();
			$ret['rk'] = array(
					'day_je'   	  => floatval($ret['xx']['day_je'])+floatval($ret['xs']['day_je'])+floatval($ret['rgcr']['day_je']),
					'day_count'	  => floatval($ret['xx']['day_count'])+floatval($ret['xs']['day_count'])+floatval($ret['rgcr']['day_count']),
					'day_user' 	  => count($rk_day_user),
					'month_je' 	  => floatval($ret['xx']['month_je'])+floatval($ret['xs']['month_je'])+floatval($ret['rgcr']['month_je']),
					'month_count' => floatval($ret['xx']['month_count'])+floatval($ret['xs']['month_count'])+floatval($ret['rgcr']['month_count']),
					'month_user'  => count($rk_month_user),
			);
			/**
			 * 彩金赠送
			 */
			$cj_day = Db::name('caijin_list')->field('SUM(cjje) as day_je,COUNT(cjje) as day_count, COUNT(DISTINCT username) as day_user')->where('username','not in',$users)->whereTime('fstime','BETWEEN',$day_time)->find();
			$cj_month = Db::name('caijin_list')->field('SUM(cjje) as month_je,COUNT(cjje) as month_count, COUNT(DISTINCT username) as month_user')->where('username','not in',$users)->whereTime('fstime','BETWEEN',$month_time)->find();
			$ret['cj'] = array_merge($cj_day,$cj_month);
			/**
			 * 返水情况
			 */
			$fs_day = Db::name('fs_list')->field('SUM(fsje) as day_je,COUNT(fsje) as day_count, COUNT(DISTINCT username) as day_user')->where('username','not in',$users)->whereTime('distribute_time','BETWEEN',$day_time)->find();
			$fs_month = Db::name('fs_list')->field('SUM(fsje) as month_je,COUNT(fsje) as month_count, COUNT(DISTINCT username) as month_user')->where('username','not in',$users)->whereTime('distribute_time','BETWEEN',$month_time)->find();
			$ret['fs'] = array_merge($fs_day,$fs_month);
			/**
			 * 人工取出
			 */
			$rgqc_day = Db::name('sgkk_list')->field('SUM(kkje) as day_je,COUNT(kkje) as day_count, COUNT(DISTINCT username) as day_user')->where('username','not in',$users)->whereTime('kktime','BETWEEN',$day_time)->find();
			$rgqc_month = Db::name('sgkk_list')->field('SUM(kkje) as month_je,COUNT(kkje) as month_count, COUNT(DISTINCT username) as month_user')->where('username','not in',$users)->whereTime('kktime','BETWEEN',$month_time)->find();
			$ret['rgqc'] = array_merge($rgqc_day,$rgqc_month);
			/**
			 * 会员提现
			 */
			$tx_day = Db::name('tixian_list')->field('SUM(txje) as day_je,COUNT(txje) as day_count, COUNT(DISTINCT username) as day_user')->where('username','not in',$users)->where('zt',1)->whereTime('txtime','BETWEEN',$day_time)->find();
			$tx_month = Db::name('tixian_list')->field('SUM(txje) as month_je,COUNT(txje) as month_count, COUNT(DISTINCT username) as month_user')->where('username','not in',$users)->where('zt',1)->whereTime('txtime','BETWEEN',$month_time)->find();
			$ret['tx'] = array_merge($tx_day,$tx_month);
			/**
			 * 出款总额
			 */
			$ck_day_user = Db::field('username')
					->table('kkty_sgkk_list')
					->group("username")->whereTime('kktime','BETWEEN',$day_time)->where('username','not in',$users)
					->union(function($query) use ($day_time,$users){
						$query->field('username')->table('kkty_tixian_list')->where('username','not in',$users)->group("username")->whereTime('txtime','BETWEEN',$day_time);
					})->select();

			$ck_month_user = Db::field('username')
					->table('kkty_sgkk_list')
					->group("username")->whereTime('kktime','BETWEEN',$month_time)->where('username','not in',$users)
					->union(function($query) use ($month_time,$users){
						$query->field('username')->table('kkty_tixian_list')->group("username")->whereTime('txtime','BETWEEN',$month_time)->where('username','not in',$users);
					})->select();
			$ret['ck'] = array(
					'day_je'   	  => floatval($ret['rgqc']['day_je'])+floatval($ret['tx']['day_je']),
					'day_count'	  => floatval($ret['rgqc']['day_count'])+floatval($ret['tx']['day_count']),
					'day_user' 	  => count($ck_day_user) ,
					'month_je' 	  => floatval($ret['rgqc']['month_je'])+floatval($ret['tx']['month_je']),
					'month_count' => floatval($ret['rgqc']['month_count'])+floatval($ret['tx']['month_count']),
					'month_user'  => count($ck_month_user),
			);
			/**
			 * 盈亏
			 */
			$ret['yk'] = array(
					'day_je'   	  => floatval($ret['rk']['day_je'])-floatval($ret['ck']['day_je']),
					'day_count'	  => '',
					'day_user' 	  => '',
					'month_je' 	  => floatval($ret['rk']['month_je'])-floatval($ret['ck']['month_je']),
					'month_count' => '',
					'month_user'  => '',
			);

			/**
			 * xj
			 */
			$xj_day_user = Db::field('username')
					->table('kkty_chongzhi_list')
					->group("username")->where(['zt'=>1,'czclass'=>1])->whereTime('cztime','BETWEEN',$day_time)->where('username','not in',$users)
					->union(function($query) use ($day_time,$users){
						$query->field('username')->table('kkty_chongzhi_list')->group("username")->where('username','not in',$users)->where(['zt'=>1,'czclass'=>2])->whereTime('cztime','BETWEEN',$day_time);
					})->union(function($query) use ($day_time,$users){
						$query->field('username')->table('kkty_chongzhi_list')->group("username")->where('username','not in',$users)->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$day_time);
					})->union(function($query) use ($day_time,$users){
						$query->field('username')->table('kkty_sgkk_list')->group("username")->where('username','not in',$users)->whereTime('kktime','BETWEEN',$day_time);
					})->union(function($query) use ($day_time,$users){
						$query->field('username')->table('kkty_tixian_list')->group("username")->where('username','not in',$users)->whereTime('txtime','BETWEEN',$day_time);
					})->union(function($query) use ($day_time,$users){
						$query->field('username')->table('kkty_caijin_list')->group("username")->where('username','not in',$users)->whereTime('fstime','BETWEEN',$day_time);
					})->union(function($query) use ($day_time,$users){
						$query->field('username')->table('kkty_fs_list')->group("username")->where('username','not in',$users)->whereTime('distribute_time','BETWEEN',$day_time);
					})->select();
			$xj_month_user = Db::field('username')
					->table('kkty_chongzhi_list')
					->group("username")->where(['zt'=>1,'czclass'=>1])->whereTime('cztime','BETWEEN',$month_time)->where('username','not in',$users)
					->union(function($query) use ($month_time,$users){
						$query->field('username')->table('kkty_chongzhi_list')->group("username")->where('username','not in',$users)->where(['zt'=>1,'czclass'=>2])->whereTime('cztime','BETWEEN',$month_time);
					})->union(function($query) use ($month_time,$users){
						$query->field('username')->table('kkty_chongzhi_list')->group("username")->where('username','not in',$users)->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$month_time);
					})->union(function($query) use ($month_time,$users){
						$query->field('username')->table('kkty_sgkk_list')->group("username")->where('username','not in',$users)->whereTime('kktime','BETWEEN',$month_time);
					})->union(function($query) use ($month_time,$users){
						$query->field('username')->table('kkty_tixian_list')->group("username")->where('username','not in',$users)->whereTime('txtime','BETWEEN',$month_time);
					})->union(function($query) use ($month_time,$users){
						$query->field('username')->table('kkty_caijin_list')->group("username")->where('username','not in',$users)->whereTime('fstime','BETWEEN',$month_time);
					})->union(function($query) use ($month_time,$users){
						$query->field('username')->table('kkty_fs_list')->group("username")->where('username','not in',$users)->whereTime('distribute_time','BETWEEN',$month_time);
					})->select();

			$ret['xj'] = array(
					'day_je'   	  => floatval($ret['rk']['day_je'])-floatval($ret['ck']['day_je'])+floatval($ret['cj']['day_je'])+floatval($ret['fs']['day_je']),
					'day_count'	  => array_sum(array_map(function($v){return $v['day_count'];},$ret)),
					'day_user' 	  => count( $xj_day_user ),
					'month_je' 	  => floatval($ret['rk']['month_je'])-floatval($ret['ck']['month_je'])+floatval($ret['cj']['month_je'])+floatval($ret['fs']['month_je']),
					'month_count' => array_sum(array_map(function($v){return $v['month_count'];},$ret)),
					'month_user'  => count( $xj_month_user ),
			);
			$data = array();
			if( $search_time != date('Y-m-d') ){
				foreach( $ret as $k=>$v ){
					$data = array(
							'search_time' => $search_time,
							'tag' => $k,
							'day_je' => floatval($v['day_je']),
							'day_count' => intval($v['day_count']),
							'day_user'=>intval($v['day_user']),
							'month_je' => floatval($v['month_je']),
							'month_count'=>intval($v['month_count']),
							'month_user'=>intval($v['month_user']),
							'create_time' => time()
					);
					Db::name('statistics_financial')->insert($data);
				}
				
			}
		}

		$this->assign('list',$ret);
		$this->assign('current',date('Y-m-d',time()));
		$this->assign('searchtime',$search_time);
		return $this->fetch('financial_statistics');
	}
	public function memberAnalysis(){
		$this->assign('current',date('Y-m-d',time()));
		$post = input('post.');
		$search_time = isset($post['searchtime']) ? $post['searchtime']: date('Y-m-d',time());
		$this->assign('searchtime',$search_time);
		
		
		$log = Db::name('statistics_member')->where(['search_time'=>$search_time])->select();
		$ret = array();
		if( count($log) > 0 ){
			foreach( $log as $v ){
				$ret[$v['tag']] = $v['val'];
			}
		}else{
			$day_begin = strtotime( $search_time.' 00:00:00' );
			$day_end = strtotime( $search_time.' 23:59:59' );
			//新增注册
			$zc = Db::name('username')->where(['swzh'=>0,'nbzh'=>0])->whereTime('reg_time','BETWEEN',[$day_begin,$day_end])->count();
			//新增存款
//			$ck = Db::name('username')->where(['swzh'=>0,'nbzh'=>0,'dl'=>0,'czzje'=>['>',0]])->whereTime('reg_time','BETWEEN',[$day_begin,$day_end])->count();
			$ck = Db::name('chongzhi_list')->where(['zt'=>1])->whereTime('cztime','BETWEEN',[$day_begin,$day_end])->group('username')->count();
			//新增代理
//			$dl = Db::name('username')->where(['dl'=>1,'tjrid'=>0])->where("tjm",['<>',null],['<>',''],'or')->whereTime('reg_time','BETWEEN',[$day_begin,$day_end])->count();
//			$dl = Db::name('username')->where(['dl'=>1])->where("tjm",['<>',null],['<>',''],'or')->whereTime('dl_time','BETWEEN',[$day_begin,$day_end])->count();
			//新增代理会员
//			$dlhy = Db::name('username')->where(['dl'=>1])->where("tjm",['eq',null],['eq',''],'or')->where('tjrid','gt',0)->whereTime('reg_time','BETWEEN',[$day_begin,$day_end])->count();
//			$dlhy = Db::name('username')->where(['dl'=>1])->where("tjm",['eq',null],['eq',''],'or')->where('tjrid','gt',0)->whereTime('dl_time','BETWEEN',[$day_begin,$day_end])->count();
			//试玩账户
			$sw = Db::name('username')->where(['swzh'=>1,'nbzh'=>0])->whereTime('reg_time','BETWEEN',[$day_begin,$day_end])->count();
			//合计
			$hj = Db::field('username')
			->table('kkty_username')->where("(swzh=0 and nbzh=0 and dl=0) or ((tjm=null or tjm='') and tjrid>0) or (swzh=1 and nbzh=0)")->whereTime('reg_time','BETWEEN',[$day_begin,$day_end])
			->union(function($query) use ($day_begin,$day_end){
				$query->field('username')->table('kkty_chongzhi_list')->where(['zt'=>1])->whereTime('cztime','BETWEEN',[$day_begin,$day_end])->group('username');
			})
			->union(function($query) use ($day_begin,$day_end){
				$query->field('username')->table('kkty_username')->where("tjm!=null or tjm!=''")->whereTime('dl_time','BETWEEN',[$day_begin,$day_end])->group('username');
			})->select();
			$ret = [
			'zc' => $zc,
			'ck' => $ck,
			'app' => 0,
			'sw' => $sw,
			'hj' => count($hj)
			];
			if( $search_time != date('Y-m-d') ){
				$data = array();
				foreach( $ret as $k=>$v ){
					$data = array(
							'search_time' => $search_time,
							'tag' => $k,
							'val' => floatval($v),
							'create_time' => time()
					);
					Db::name('statistics_member')->insert($data);
				}
				
			}
		}
		$this->assign('list',$ret);
		return $this->fetch('member_analysis');
	}
	
	public function financialDetail(){
	    $type = input('get.type');
	    $start_time = input('get.searchtime');
	    $end_time = strtotime($start_time.' 23:59:59' );
	    $time = [$start_time,$end_time];
	    $user = Db::name('username')->where('nbzh',1)->column('username');
	    $users = implode(',', $user);
	    switch ($type){
	         
	        case 'xx':
	            $list = Db::name('chongzhi_list')->field('username,je,cztime as time')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>1])->whereTime('cztime','BETWEEN',$time)->select();
	            break;
	        case 'xs':
	            $list = Db::name('chongzhi_list')->field('username,je,cztime as time')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>2])->whereTime('cztime','BETWEEN',$time)->select();
	            break;
	        case 'rgcr':
	            $list = Db::name('chongzhi_list')->field('username,je,cztime as time')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$time)->select();
	            break;
	        case 'rk':
	            $xx = Db::name('chongzhi_list')->field('username,je,cztime as time')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>1])->whereTime('cztime','BETWEEN',$time)->select();
	            $sx = Db::name('chongzhi_list')->field('username,je,cztime as time')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>2])->whereTime('cztime','BETWEEN',$time)->select();
	            $sg = Db::name('chongzhi_list')->field('username,je,cztime as time')->where('username','not in',$users)->where(['zt'=>1,'czclass'=>3])->whereTime('cztime','BETWEEN',$time)->select();
	            $list = array_merge($xx,$sx,$sg);
	            break;
	        case 'cj':
	            $list = Db::name('caijin_list')->field('username,cjje as je,fstime as time')->where('username','not in',$users)->whereTime('fstime','BETWEEN',$time)->select();
	            break;
	        case 'fs':
	            $list = Db::name('fs_list')->field('username,fsje as je,distribute_time as time')->where('username','not in',$users)->whereTime('distribute_time','BETWEEN',$time)->select();
	            break;
	        case 'rgqc':
	            $list = Db::name('sgkk_list')->field('username,kkje as je,kktime as time')->where('username','not in',$users)->whereTime('kktime','BETWEEN',$time)->select();
	            break;
	        case 'tx':
	            $list = Db::name('tixian_list')->field('username,txje as je,txtime as time')->where('username','not in',$users)->where('zt',1)->whereTime('txtime','BETWEEN',$time)->select();
	            break;
	        case 'ck':
	            $rgqc = Db::name('sgkk_list')->field('username,kkje as je,kktime as time')->where('username','not in',$users)->whereTime('kktime','BETWEEN',$time)->select();
	            $tx = Db::name('tixian_list')->field('username,txje as je,txtime as time')->where('username','not in',$users)->where('zt',1)->whereTime('txtime','BETWEEN',$time)->select();
	            $list = array_merge($rgqc,$tx);
	            break;
	    }
	    $this->assign('list',$list);
	    return $this->fetch('financial_detail');
	}
	
    //会员统计
    public function memberStatistics(){
		
        return $this->fetch('member_statistics');
    }
    public function memberto(){
    	$post = input('post.');
    	$search_time = isset($post['searchtime']) ? $post['searchtime']: date('Y-m-d',time());
    	$this->assign('searchtime',$search_time);
    	
    	//流失人数统计
    	$loss_model = new TouzhuListOk();
    	$loss_search = -1;
    	//指定日期流失
    	$searchscope = isset($post['searchscope']) && !empty($post['searchscope']) ? intval($post['searchscope']) : -1;
    	
    	$log = Db::name('statistics_track')->where(['search_time'=>$search_time,'type'=>0])->select();
    	$loss_data = array();
    	if( count($log) > 0 ){
    		foreach( $log as $v ){
    			switch( intval($v['tag']) ){
    				case 0:
    					$loss_data['loss_three'] = intval($v['val']);
    					break;
    				case 1:
    					$loss_data['loss_week'] = intval($v['val']);
    					break;
    				case 2:
    					$loss_data['loss_month'] = intval($v['val']);
    					break;
    				default:
    			}
    		}
    		if( -1 != $searchscope ){
    			$searchlog = Db::name('statistics_track')->where(['search_time'=>$search_time,'type'=>1,'tag'=>$searchscope])->find();
    			if(empty($searchlog)){
    				$search_region = strtotime($search_time.' -'.$searchscope.' day');
    				$loss_data['loss_search'] = $loss_model->lossMemberBetweenInfo($search_region,strtotime($search_time));
    				if( $search_time != date('Y-m-d') ) {
    					Db::name('statistics_track')->insert(['search_time'=>$search_time,'tag'=>$searchscope,'val'=>$loss_data['loss_search'],'type'=>1,'create_time'=>time()]);
    				}
    			}else{
    				$loss_data['loss_search'] = $searchlog['val'];
    			}
    		}else{
    			$loss_data['loss_search'] = $loss_search;
    		}
    	}else{
    		//近三日流失
    		$three_region = strtotime($search_time.' -3 day');
    		$loss_three = $loss_model->lossMemberBetweenInfo($three_region,strtotime($search_time));
    		//近一周流失
    		$week_region = strtotime($search_time.' -7 day');
    		$loss_week = $loss_model->lossMemberBetweenInfo($week_region,strtotime($search_time));
    		//近一月流失
    		$month_region = strtotime($search_time.' -30 day');
    		$loss_month = $loss_model->lossMemberBetweenInfo($month_region,strtotime($search_time));
    	
    		if( -1!=$searchscope ){
    			$search_region = strtotime($search_time.' -'.$searchscope.' day');
    			$loss_search = $loss_model->lossMemberBetweenInfo($search_region,strtotime($search_time));
    		}
    	
    		$loss_data = array(
    				'loss_three' => $loss_three,
    				'loss_week' => $loss_week,
    				'loss_month' => $loss_month,
    				'loss_search' => $loss_search
    		);
    		if( $search_time != date('Y-m-d') ) {
    			$data = array();
    			foreach ($loss_data as $k => $v) {
    				$val['search_time'] = $search_time;
    				$val['tag'] = 0;
    				$val['type'] = 0;
    				$val['create_time'] = time();
    				if ('loss_week' == $k) {
    					$val['tag'] = 1;
    				} elseif ('loss_month' == $k) {
    					$val['tag'] = 2;
    				} elseif ('loss_search' == $k) {
    					continue;
    				}
    				$val['val'] = intval($v);
    				$data = $val;
    				Db::name('statistics_track')->insert($data);
    			}
    			
    		}
    	}
    	$this->assign('current',date('Y-m-d',time()));
    	$this->assign('searchscope',$searchscope);
    	$this->assign('loss_data',$loss_data);
    	return $this->fetch('member_to');
    }
	public function memberTj_old(){
		$type =1;

			$user = input('us_sql','');
            $sDate1 = input('sDate1',date('Y-m-d',strtotime('-30 days')));
			$sDate2 = input('sDate2',date('Y-m-d',time()));
            if($user){
                $where = array('username' => $user );
                $w = array('u.username' => $user );
            }else{
                $where = array();
                $w = array();
            }
			$start = strtotime($sDate1.' 00:00:00');
			$end = strtotime($sDate2.' 23:59:59');
			$condition = [$start,$end];
			$ck_query = Db::name('chongzhi_list')
			->field('username,sum(je) as czzje')
            ->where($where)
			->where('zt',1)->whereTime('cztime','BETWEEN',$condition)
			->group('username')
			->buildSql();
			$tx_query = Db::name('tixian_list')
			->field('username,sum(txje) as txzje')
            ->where($where)
			->where('zt',1)->whereTime('txtime','BETWEEN',$condition)
			->group('username')
			->buildSql();
			$tz_query = Db::name('touzhu_list_ok')
			->field('username,sum(tzje) as tzzje,sum(zjje) as zjzje')
			->whereTime('tz_time','BETWEEN',$condition)
            ->where($where)
			->group('username')
			->buildSql();
			$list = Db::name('username')->alias('u')
			->join("{$ck_query} c",'c.username=u.username','left')
			->join("{$tx_query} t",'t.username=u.username','left')
			->join("{$tz_query} z",'z.username=u.username','left')
			->where(['u.swzh'=>0,'u.nbzh'=>0])
            ->where($w)
			->field('u.username,c.czzje,t.txzje,z.tzzje,z.zjzje')
			->paginate(10,false,['query'=>['type'=>$type,'sDate1'=>input('sDate1'),'sDate2'=>input('sDate2')]])
			->each(function($item,$key) {
				$item['profit_and_loss'] = floatval($item['tzzje']) - floatval($item['zjzje']);
				$item['czzje'] = floatval($item['czzje']);
				$item['txzje'] = floatval($item['txzje']);
				if($item['tzzje'] == 0) {
					$item['kill'] = '无';
				}else {
					$item['kill'] = sprintf('%.2f',(floatval($item['tzzje']) - floatval($item['zjzje'])) / floatval($item['tzzje']) * 100);
				}
				return $item;
			});
		
				$total_info = Db::name('username')->alias('u')
				->join("{$ck_query} c",'c.username=u.username','left')
				->join("{$tx_query} t",'t.username=u.username','left')
				->join("{$tz_query} z",'z.username=u.username','left')
				->where(['u.swzh'=>0,'u.nbzh'=>0])
                ->where($w)
				->field('"总计" as username,SUM(c.czzje) as czzje,SUM(t.txzje) as txzje,SUM(z.tzzje) as tzzje,SUM(z.zjzje) as zjzje,SUM(z.tzzje)-SUM(z.zjzje) as profit_and_loss')
				->find();
				if(empty($total_info['tzzje'])){
					$total_info['kill'] = '无';
				}else{
					$total_info['kill'] = sprintf('%.2f',($total_info['tzzje'] - $total_info['zjzje']) / $total_info['tzzje'] * 100);
				}
//		}
		$this->assign('start',$sDate1);
		$this->assign('end',$sDate2);
		
		//当前页总计
		$cczje = 0;
		$ctxje = 0;
		$cur_profit_loss = 0;
		$ctzzje = 0;
		foreach($list as $vl) {
			$cczje += $vl['czzje'];
			$ctxje += $vl['txzje'];
			$cur_profit_loss += $vl['tzzje'] - $vl['zjzje'];
			$ctzzje += $vl['tzzje'];
		}
		if( empty($ctzzje) ){
			$ckill = '无';
		}else{
			$ckill = sprintf('%.2f',$cur_profit_loss / $ctzzje * 100);
		}
		$cur_total = array(
				'username' => '当前页总计',
				'czzje' => $cczje,
				'txzje' => $ctxje,
				'profit_and_loss' => $cur_profit_loss,
				'kill' => $ckill
		);
		$totalpage = $list->lastPage();
		$footer[] = $total_info;
		if( intval($totalpage) > 1 ){
			array_unshift($footer,$cur_total);
		}
		$this->assign('list',$list);
		$this->assign('cur_list',$footer);
		
		//return $this->fetch('member_tj');
	}
    //注单统计
    public function betStatistics()
    {
		$condition = 'today';
		$select = intval(input('get.select',0));
		$start = input('sDate1') ? strtotime(input('sDate1').' 00:00:00') : time();
		$end = input('sDate2') ? strtotime(input('sDate2').' 23:59:59') : time();
        $id = input('id') ? input('id') :0;
        $caipiao = Db::name('caipai_list')->field('id,cp_name_old,cp_name_new')->where('cp_if_open',1)->order('id','DESC')->select();

		if( 1==$select ) {
			$condition = [$start,$end];
		}elseif( 2==$select ){
			$start = strtotime(date("Y-m-d 00:00:00",time()));
			$end = strtotime(date("Y-m-d 23:59:59",time()));
			$condition = 'today';
		}elseif( 3==$select ){
			$start = strtotime( date("Y-m-d 00:00:00",strtotime("-1 day")) );
			$end = strtotime( date("Y-m-d 23:59:59",strtotime("-1 day")) );
			$condition = 'yesterday';
		}elseif( 4==$select ){
			$start = mktime( 0, 0 , 0, date("m"), date("d")-date("w")+1, date("Y") );
			$end = mktime( 23,59,59,date("m"), date("d")-date("w")+7, date("Y") );
			$condition = 'week';
		}elseif( 5==$select ){
			$start = mktime(0, 0 , 0,date("m"),1,date("Y"));
			$end = mktime(23,59,59,date("m"),date("t"),date("Y"));
			$condition = 'month';
		}elseif( 6==$select ){
			$start = mktime(0, 0 , 0,date("m")-1,1,date("Y"));
			$end = mktime(23,59,59,date("m") ,0,date("Y"));
			$condition = 'last month';
		}
		$total_money = 0;
		if( is_array($condition) ){
			$list_query = Db::name('touzhu_list_ok')->alias("a")
					->join("username b","a.username=b.username",'left')
					->field('a.czid,COUNT(a.id) as count,SUM(a.tzje) as tzje,SUM(a.zjje) as zjje,COUNT(DISTINCT a.username) as user_count')
					->whereTime('a.tz_time','BETWEEN',$condition)
					->where(['b.nbzh'=>0,'b.swzh'=>0])
					->group('czid')
					->buildSql();
			$total_info = Db::name('caipai_list')->alias('cl')
					->join('touzhu_list_ok tlo','tlo.czid=cl.id')
					->join('username b','tlo.username=b.username','left')
					->field('COUNT(tlo.id) as zcount,SUM(tlo.tzje) as ztzje,SUM(tlo.zjje) as zzjje,COUNT(DISTINCT tlo.username) as zuser_count')
					->where('cl.cp_if_open',1)
					->whereTime('tlo.tz_time','BETWEEN',$condition)
					->where(['b.nbzh'=>0,'b.swzh'=>0])->find();
		}else{
			$list_query = Db::name('touzhu_list_ok')->alias("a")
					->join("username b","a.username=b.username",'left')
					->field('a.czid,COUNT(a.id) as count,SUM(a.tzje) as tzje,SUM(a.zjje) as zjje,COUNT(DISTINCT a.username) as user_count')
					->whereTime('a.tz_time',$condition)
					->where(['b.nbzh'=>0,'b.swzh'=>0])
					->group('czid')
					->buildSql();
			$total_info = Db::name('caipai_list')->alias('cl')
					->join('touzhu_list_ok tlo','tlo.czid=cl.id')
					->join('username b','tlo.username=b.username','left')
					->field('COUNT(tlo.id) as zcount,SUM(tlo.tzje) as ztzje,SUM(tlo.zjje) as zzjje,COUNT(DISTINCT tlo.username) as zuser_count')
					->where('cl.cp_if_open',1)
					->whereTime('tlo.tz_time',$condition)
					->where(['b.nbzh'=>0,'b.swzh'=>0])->find();
		}
        if($id != 0){
            $where = array('cl.id'=>$id);
        }else{
            $where = array();
        }
		$list = Db::name('caipai_list')->alias('cl')
				->join("{$list_query} tlo",'tlo.czid=cl.id','left')
				->field('cl.cp_name_old as name,cl.id as id,tlo.*')
				->where('cl.cp_if_open',1)
                ->where($where)
				->group('cl.cp_name_old,tlo.czid')
				->select();
		$total_info["zhushu_zong"]=0;
        $total_info["zhushu_ying"]=0;
        $total_info["zhushu_shu"]=0;
        $total_info["shenglv"]="";
        $total_info['dengji']="无";
        //总计
		foreach($list as &$v) {
			$v['count'] = empty($v['count']) ? 0 :intval($v['count']);
			$v['tzje'] = empty($v['tzje']) ? 0 :floatval($v['tzje']);
			$v['zjje'] = empty($v['zjje']) ? 0 :floatval($v['zjje']);
			$v['user_count'] = empty($v['user_count']) ? 0 :intval($v['user_count']);
			$total_money += $v['tzje'];
			$v['profit_and_loss'] = $v['tzje'] - $v['zjje'];
			if( empty($v['tzje']) ){
				$v['kill'] = '无';
			}else{
				$v['kill'] = sprintf('%.2f',($v['tzje'] - $v['zjje']) / $v['tzje'] * 100);
			}
            $v['zhushu_zong']=Db::name('touzhu_list_ok')->whereTime('tz_time',$condition)->where('czid',$v['id'])->sum('tzzs');//总注数
            $v['zhushu_ying']=Db::name('touzhu_list_ok')->whereTime('tz_time',$condition)->where('czid',$v['id'])->where('zjje','>',0)->sum('tzzs');//赢（注数）
            $v['zhushu_shu']=Db::name('touzhu_list_ok')->whereTime('tz_time',$condition)->where('czid',$v['id'])->where('zjje',0)->sum('tzzs');//输（注数）
            $v['shenglv']="";//胜率
            $v['dengji']="无";//等级
                if($v['zhushu_zong']!=0){
                    $v['shenglv']=intval($v['zhushu_ying'])/intval($v['zhushu_zong'])*100;
                    $v['shenglv']=round($v['shenglv'],2);
                    if($v['shenglv']>=50){
                        $v['dengji']="高";
                    }elseif($v['shenglv']<=30){
                        $v['dengji']="低";
                    }else{
                        $v['dengji']="中";
                    }
                }
            $total_info["zhushu_zong"]+=$v['zhushu_zong'];
            $total_info["zhushu_ying"]+=$v['zhushu_ying'];
            $total_info["zhushu_shu"]+=$v['zhushu_shu'];
            if($total_info['zhushu_zong']!=0){
                    $total_info['shenglv']=intval($total_info['zhushu_ying'])/intval($total_info['zhushu_zong'])*100;
                    $total_info['shenglv']=round($total_info['shenglv'],2);
                    if($total_info['shenglv']>=50){
                        $total_info['dengji']="高";
                    }elseif($total_info['shenglv']<=30){
                        $total_info['dengji']="低";
                    }else{
                        $total_info['dengji']="中";
                    }
                }
			/*
             * 若杀率是按照投注个数计算
            if(isset($zj_count[$v['czid']])) {
                $v['kill'] = sprintf('%.2f',($v['count'] - $zj_count[$v['czid']]) / $v['count'] * 100);
            } else {
                $v['kill'] = '100.00';
            }
             */
		}
        
		$total_info['all_profit_loss'] = $total_info['ztzje'] - $total_info['zzjje'];
        $this->assign('caipiao',$caipiao);
        $this->assign('id',$id);
		$this->assign('start',date('Y-m-d',$start));
		$this->assign('end',date('Y-m-d',$end));
		$this->assign('total_money',$total_money);
		$this->assign('list',$list);
		$this->assign('select',$select);
		$this->assign('total',$total_info);
		return $this->fetch('bet_statistics');
    }
    

    public function betDetail(){
        $start_time = input('get.start') ? input('get.start') : '';
        $end_time = input('get.end') ? strtotime(input('get.end').' 23:59:59') : time();
        $time = [$start_time,$end_time];
        $czid = input('get.type');
    
        $where = array();
        $where['t.czid'] = $czid;
        if($czid == 'all'){
            $codeType = Db::name('caipai_list')->where('cp_if_open',1)->column('id');
            $where = ['t.czid'=>['in',$codeType]];
        }
    
        $list = Db::name('touzhu_list_ok')
        		->alias('t')
        		->join('__USERNAME__ u','u.username=t.username')
        		->where($where)
        		->where('u.nbzh','<>','1')
		        ->field('t.username,t.tzje,t.tzzs,t.tzje,t.zjje,t.tz_time')
		        ->whereTime('t.tz_time','BETWEEN',$time)
		        ->select();
        $this->assign('list',$list);
        return $this->fetch('bet_detail');
    }
    
    //代理统计
    public function agentStatistics(){
    	
    	$total_money = 0;
        $limit=input('limit')?input('limit'):20;

        $this->assign('limit',$limit);
        $username=input('us_sql','');
        $start = input('sDate1')?strtotime(input('sDate1').' 00:00:00'):0;
        $end = strtotime(input('sDate2',date('Y-m-d')) .date('H:i:s'));
        $time = [$start,$end];
        $where = array();
        if($username){
            $where['username'] = ['like','%'.$username.'%'];
        }

        $this->assign('username',$username);

    	$list = Db::name('username')->where(['tjm'=>['<>',''],'nbzh'=>0,'swzh'=>0])
                ->where($where)
                ->where('reg_time','between',$time)
                ->paginate($limit)
                ->each(function($item,$key){

    				if($item['tjr']!=''){
    					$item['dlfandian'] = Db::name('username')->where('username',$item['tjr'])->value('fandian');
    				}else{
    					$item['dlfandian'] = 0;
    				}

    				$item['xsyj'] = Db::name('agent_balance')->where(['agent'=>$item['username']])->value('SUM(balance)');
    				$item['zshyrs'] = $this->zsyj($item['username']);
    				$ret = array();
    				$this->xishuMemberTotal($item['username'],0,$ret);
    				$item['xszs'] =empty($ret) ? 0 : max($ret);
    				return $item;
    			});
    	
    	//总计
    	$total_info = array('fandian'=>0,'dlfandian'=>0,'je'=>0,'xszs'=>0,'xsyj'=>0,'xjrens'=>0,'zshyrs'=>[0,0,0,0,0,0],'ykqk'=>0,'sl'=>0);
    	foreach ($list as $v){
    		$total_info['fandian']+=$v['fandian'];
    		$total_info['dlfandian'] += $v['dlfandian'];
    		$total_info['je'] += $v['je'];
    		$total_info['xszs'] += $v['xszs'];
    		$total_info['xsyj'] += $v['xsyj'];
    		$total_info['xjrens'] += $v['xjrens'];
    		$total_info['zshyrs'][0] += $v['zshyrs'][0];
    		$total_info['zshyrs'][1] += $v['zshyrs'][1];
    		$total_info['zshyrs'][2] += $v['zshyrs'][2];
    		$total_info['zshyrs'][3] += $v['zshyrs'][3];
    		$total_info['zshyrs'][4] += $v['zshyrs'][4];
    		$total_info['zshyrs'][5] += $v['zshyrs'][5];
    		
    	}
    	$total_info['ykqk'] = $total_info['zshyrs'][3]-$total_info['zshyrs'][5];
    	$total_info['sl'] = empty($total_info['zshyrs'][3]) ? 0 : sprintf('%.2f',$total_info['ykqk']/$total_info['zshyrs'][3]*100);
    	

		
    	$this->assign('total_money',$total_money);
    	$this->assign('list',$list);
    	$this->assign('total',$total_info);
    	return $this->fetch('agent_statistics');
    }
	public function excelList(){
		$lists = Db::name('username')->where(['tjm'=>['<>','']])->select();
		foreach ($lists as $key=>&$item){
			if($item['tjr']!=''){
				$item['dlfandian'] = Db::name('username')->where('username',$item['tjr'])->value('fandian');
			}else{
				$item['dlfandian'] = 0;
			}
			 
			$item['xsyj'] = Db::name('agent_balance')->where(['agent'=>$item['username']])->value('SUM(balance)');
			$item['zshyrs'] = $this->zsyj($item['username']);
			$ret = array();
			$this->xishuMemberTotal($item['username'],0,$ret);
			$item['xszs'] =empty($ret) ? 0 : max($ret);
		
		};
		
		$total_infos = array('fandian'=>0,'dlfandian'=>0,'je'=>0,'xszs'=>0,'xsyj'=>0,'xjrens'=>0,'zshyrs'=>[0,0,0,0,0,0],'ykqk'=>0,'sl'=>0);
		foreach ($lists as $v){
			$total_infos['fandian']+=$v['fandian'];
			$total_infos['dlfandian'] += $v['dlfandian'];
			$total_infos['je'] += $v['je'];
			$total_infos['xszs'] += $v['xszs'];
			$total_infos['xsyj'] += $v['xsyj'];
			$total_infos['xjrens'] += $v['xjrens'];
			$total_infos['zshyrs'][0] += $v['zshyrs'][0];
			$total_infos['zshyrs'][1] += $v['zshyrs'][1];
			$total_infos['zshyrs'][2] += $v['zshyrs'][2];
			$total_infos['zshyrs'][3] += $v['zshyrs'][3];
			$total_infos['zshyrs'][4] += $v['zshyrs'][4];
			$total_infos['zshyrs'][5] += $v['zshyrs'][5];
			 
		}
		$total_infos['ykqk'] = $total_infos['zshyrs'][3]-$total_infos['zshyrs'][5];
		$total_infos['sl'] = empty($total_infos['zshyrs'][3]) ? 0 : sprintf('%.2f',$total_infos['ykqk']/$total_infos['zshyrs'][3]*100);
		
		if(input('sure')==1){
			$sucess = $this->createtable($lists, $total_infos,'代理报表');
		} 
	}
    function createtable($list,$total_info,$filename){

    	header("Content-type:application/vnd.ms-excel");
    	header("Content-Disposition:filename=".$filename.".xls");
    
    	$strexport="代理商\t佣金返点\t上级代理\t上级佣金返点\t账户余额\t系属会员总数\t系属佣金\t直属会员总数\t直属佣金\t直属存款会员数\t直属存款总额\t直属取款总额\t直属总投注\t直属总派彩\t盈亏情况 [投注金额-中奖金额]\t杀率(%) [(投注金额-中奖金额)/投注金额]\r";
    	foreach ($list as $row){
    		$strexport.=$row['username']."\t";
    		$strexport.=$row['fandian']."\t";
    		$strexport.=$row['tjr']."\t";
    		$strexport.=$row['dlfandian']."\t";
    		$strexport.=$row['je']."\t";
    		$strexport.=$row['xszs']."\t";
    		$strexport.=$row['xsyj']."\t";
    		$strexport.=$row['xjrens']."\t";
    		$strexport.=$row['zshyrs']['4']."\t";
    		$strexport.=$row['zshyrs']['1']."\t";
    		$strexport.=$row['zshyrs']['0']."\t";
    		$strexport.=$row['zshyrs']['2']."\t";
    		$strexport.=$row['zshyrs']['3']."\t";
    		$strexport.=$row['zshyrs']['5']."\t";
    		$strexport.=$row['zshyrs']['6']."\t";
    		$strexport.=$row['zshyrs']['7']."\r";
    	}
    	$strexport.="总计\t";
    	$strexport.=$total_info['fandian']."\t";
    	$strexport.=" \t";
    	$strexport.=$total_info['dlfandian']."\t";
    	$strexport.=$total_info['je']."\t";
    	$strexport.=$total_info['xszs']."\t";
    	$strexport.=$total_info['xsyj']."\t";
    	$strexport.=$total_info['xjrens']."\t";
    	$strexport.=$total_info['zshyrs']['4']."\t";
    	$strexport.=$total_info['zshyrs']['1']."\t";
    	$strexport.=$total_info['zshyrs']['0']."\t";
    	$strexport.=$total_info['zshyrs']['2']."\t";
    	$strexport.=$total_info['zshyrs']['3']."\t";
    	$strexport.=$total_info['zshyrs']['5']."\t";
    	$strexport.=$total_info['ykqk']."\t";
    	$strexport.=$total_info['sl']."\r";
    	$strexport=iconv('UTF-8',"GB2312//IGNORE",$strexport);
        $this->member_model->caozuorizhi_xieru('导出代理统计的Excel表');
    	exit($strexport);
    }
    function zsyj($user){
    	$it=[0,0,0,0,0,0,0,0];
    	$users = Db::name('username')->where('tjr',$user)->select();
    	foreach ($users as $value){
    		$s = Db::name('agent_balance')->where(['agent'=>$user,'consumer'=>$value['username'],'higher_agent'=>$value['username']])->value('SUM(balance)');
    		$q = Db::name('tixian_list')->where(['username'=>$value['username'],'zt'=>1,'czadmin'=>['<>','']])->value('SUM(txje)');
    		$z = Db::name('chongzhi_list')->where(['username'=>$value['username'],'zt'=>1,'czadmin'=>['<>','']])->value('SUM(je)');
    		$tz = Db::name('touzhu_list_ok')->where(['username'=>$value['username']])->value('SUM(tzje)');
    		$pc = Db::name('touzhu_list_ok')->where(['username'=>$value['username'],'zjje'=>['>','0']])->value('SUM(zjje)');
    		$it[4] += $s;
    		$it[0] += $z;
    		if($z>0){
    			$it[1] +=1;
    		}
    		$it[2] += $q;
    		$it[3] += $tz;
    		$it[5] += $pc;
    		
    	}
    	$it[6] = $it[3]-$it[5];
    	$it[7] = empty($it[3])? 0 : sprintf('%.2f',$it[6]/$it[3]*100);
    	return $it;
    }
    function xishuMemberTotal($user,$i,&$ret){

    	$users = Db::name('username')->where('tjr',$user)->select();
    	foreach($users as $v){
    		$i =$i+1;
    		$ret[] = $i;
    		
    			$this->xishuMemberTotal($v['username'],$i,$ret);
    		
    	}
    }
    //版本管理
    public function versionManagement(){
    	$type = input('type',0);
    	$system = input('system',0);
    	$list = Db::name('version')->where(['type'=>$type,'systemtype'=>$system])->order('number','DESC')->paginate(10,false,array('query'=>request()->param()))
    			->each(function($item, $key){
						if($item['systemtype']==1) $item['version_url'] = $_SERVER['REQUEST_SCHEME'].'://'.$_SERVER['HTTP_HOST'].'/apk/'.$item['version_url'];
						if($item['systemtype']==2) $item['version_url'] = 'itms-services://?action=download-manifest&url='.$_SERVER['REQUEST_SCHEME'].'://'.$_SERVER['HTTP_HOST'].'/ios/'.$item['version_url'];
						return $item;
					});

    	$this->assign('list',$list);
    	return $this->fetch('version_managenment');
    }
    /**
     * 还没做的地方
     * @return Ambigous <\think\mixed, string>
     */
    public function versionType(){

		$type = Db::name('version_type')->select();
		$list = array();
		foreach($type as $val){
			$ios = Db::name('version')->where(['type'=>$val['id'],'systemtype'=>2])->order('number DESC')->find();
			if( !empty( $ios ) ){
				$ios['classtype'] = $val['classtype'];
				$ios['version_url'] = 'itms-services://?action=download-manifest&url='.$_SERVER['REQUEST_SCHEME'].'://'.$_SERVER['HTTP_HOST'].'/ios/'.$ios['version_url'];
				$list[] = $ios;
			}
			$android = Db::name('version')->where(['type'=>$val['id'],'systemtype'=>1])->order('number DESC')->find();
			if( !empty( $android ) ){
				$android['classtype'] = $val['classtype'];
				$android['version_url'] = $_SERVER['REQUEST_SCHEME'].'://'.$_SERVER['HTTP_HOST'].'/apk/'.$android['version_url'];
				$list[] = $android;
			}
		}
    	$this->assign('list',$list);
    	return $this->fetch('version_type');
    }
    public function addVersionType(){
    	
    	return $this->fetch('add_version_type');
    }
    public function addVersion(){
    	if(input('post.')){
    		$data['upgrade'] = input('upgrade');
	    	$data['version'] = input('version');
	    	$data['number'] = input('number');
			$data['desc'] = !empty(input('desc')) ? htmlentities(input('desc')) : '';
	    	$data['type'] = input('type');
	    	$data['url_time'] = time();
	    	$data['systemtype'] = input('systemtype');
			if( 1==intval(input('systemtype')) ){
				if( !$version_url = $this->upload() ){
					js_tishi('apk文件上传错误', 'addVersion');exit;
				}
				$data['version_url'] = $version_url;
			}elseif( 2==intval(input('systemtype')) ){
				if( !$version_url = $this->upload('file',1) ){
					js_tishi('ipa文件上传错误', 'addVersion');exit;
				}
				$data['version_ext'] = $version_url;
				$version_arr = explode('.',$version_url);
				$arr = [
					'{URL}'=>$_SERVER['REQUEST_SCHEME'].'://'.$_SERVER['HTTP_HOST'].'/ios/'.$version_arr[0].'.ipa',
					'{VERSION}'=> $data['version']
				];
				if( !useTemplate(ROOT_PATH.'public'.DS.'template'.DS.'version_1.plist',ROOT_PATH.'public'.DS.'ios'.DS.$version_arr[0].'.plist',$arr) || !useTemplate(ROOT_PATH.'public'.DS.'template'.DS.'version_1.plist',ROOT_PATH.'public'.DS.'ios'.DS.'manifest.plist',$arr) ){
					js_tishi('plist文件上传错误', 'addVersion');exit;
				}
				$data['version_url'] = $version_arr[0].'.plist';
			}
	    	if(Db::name('version')->insert($data)){
	    		$this->redirect('versionType');
	    	}else{
	    		js_tishi('错误', 'versionType');
	    	}
    	}else{
    		$query = Db::name('version_type')->where('status',1)->select();
    		$this->assign('que',$query);
    		return $this->fetch('add_version');
    	}
    }
    public function saveVersion(){
    	$query = Db::name('version')->where('id',input('uid'))->find();
    	if(input('post.')){
    		$data['upgrade'] = input('upgrade');
    		$data['version'] = input('version');
    		$data['number'] = input('number');
    		$data['desc'] = !empty(input('desc')) ? htmlentities(input('desc')) : '';
    		$data['systemtype'] = input('systemtype');

			if( 1==intval(input('systemtype')) ){
				if( $version_url = $this->upload() ) $data['version_url'] = $version_url;
			}elseif( 2==intval(input('systemtype')) ){
				if( $version_url = $this->upload('file',1) ){
					$version_arr = explode('.',$version_url);
					$arr = [
							'{URL}'=>$_SERVER['REQUEST_SCHEME'].'://'.$_SERVER['HTTP_HOST'].'/ios/'.$version_arr[0].'.ipa',
							'{VERSION}'=> $data['version']
					];
					if( useTemplate(ROOT_PATH.'public'.DS.'template'.DS.'version_1.plist',ROOT_PATH.'public'.DS.'ios'.DS.$version_arr[0].'.plist',$arr) && useTemplate(ROOT_PATH.'public'.DS.'template'.DS.'version_1.plist',ROOT_PATH.'public'.DS.'ios'.DS.'manifest.plist',$arr) ){
						$data['version_ext'] = $version_url;
						$data['version_url'] = $version_arr[0].'.plist';
					}
				}
			}
    		$data['type'] = input('type');
    		$data['url_time'] = time();
    		if(Db::name('version')->where('id',input('uid'))->update($data)){
    			js_tishi('ok','versionManagement?type='.input('type').'&system='.input('system'));
    		}else{
    			js_tishi('错误', 'versionManagement?type='.input('type').'&system='.input('system'));
    		}
    	}
    	$q = Db::name('version_type')->where('status',1)->select();
    	$this->assign('que',$q);
    	$this->assign('query',$query);
    	return $this->fetch('add_version');
    	
    }
    public function VersionDel(){
    	$uid = input('uid');

		$version = Db::name('version')->where('id',$uid)->find();
		$lastest = Db::name('version')->where(['type'=>$version['type'],'systemtype'=>$version['systemtype']])->order('number DESC')->limit(2)->select();
		if( count($lastest) > 1 ){
			foreach($lastest as $k=>$v){
				if( 0==$k && intval($version['id'])==intval($v['id']) ){
					$content = file_get_contents(ROOT_PATH.'public'.DS.'ios'.DS.$lastest[1]['version_url']);
					file_put_contents(ROOT_PATH.'public'.DS.'ios'.DS.'manifest.plist',$content);
					break;
				}
			}
		}
    	if(Db::name('version')->where('id',$uid)->delete()){
    		js_tishi('ok','versionManagement?type='.input('type').'&system='.input('system'));
    	}
    }
    public function upload($upload='file',$type=0){
    	// 获取表单上传文件 例如上传了001.jpg
    	$file = request()->file( $upload );
    	// 移动到框架应用根目录/public/uploads/ 目录下
    	if($file){
			if( 0==$type ){
				$path = ROOT_PATH . 'public' . DS . 'apk';
			}else{
				$path = ROOT_PATH . 'public' . DS . 'ios';
			}
			$info = $file->move($path);
    		if($info){
    			return $info->getSaveName();
    		}else{
    			return false;
    		}
    	}
    }
    public function userStatistics(){
		$lab = input('lab','');
		$search = input('search','');
		$day_begin = strtotime( $search.' 00:00:00' );
		$day_end = strtotime( $search.' 23:59:59' );
		$list = array();
		switch($lab){
			case 'zc':
				$list = Db::name('username')->where(['swzh'=>0,'nbzh'=>0])->whereTime('reg_time','BETWEEN',[$day_begin,$day_end])->field("id,username")->paginate(10,false,array('query'=>request()->param()));
				break;
			case 'ck':
				$list = Db::name('chongzhi_list')->where(['zt'=>1])->whereTime('cztime','BETWEEN',[$day_begin,$day_end])->group('username')->paginate(10,false,array('query'=>request()->param()));
				break;
			case 'dl':
				$list = Db::name('username')->where("tjm",['<>',null],['<>',''],'or')->whereTime('dl_time','BETWEEN',[$day_begin,$day_end])->paginate(10,false,array('query'=>request()->param()));
				break;
			case 'dlhy':
				$list = Db::name('username')->where("tjm",['eq',null],['eq',''],'or')->where('tjrid','gt',0)->whereTime('dl_time','BETWEEN',[$day_begin,$day_end])->paginate(10,false,array('query'=>request()->param()));
				break;
			case 'app':
				
				break;
			case 'sw':
				$list = Db::name('username')->where(['swzh'=>1,'nbzh'=>0])->whereTime('reg_time','BETWEEN',[$day_begin,$day_end])->paginate(10,false,array('query'=>request()->param()));
				break;
		}
		$this->assign('query',$list);
		return $this->fetch('user_statistics');
	}
	public function commissionDateDetail(){
		$user = input("name",'');
		$balance = Db::name('agent_balance')->group("FROM_UNIXTIME(add_time,'%Y-%m-%d')")->field("FROM_UNIXTIME(add_time,'%Y-%m-%d') as in_time,sum(balance) as total")->where(['agent'=>$user,'status'=>1])->select();
		$this->assign('list',$balance);
		$this->assign('user',$user);
		return $this->fetch('commission_dateDetail');
	}
	public function commissionDetail(){
		$user = input("name",'');
		$date = input("in",'');
		$begin = strtotime($date.' 00:00:00');
		$end = strtotime($date.' 23:59:59');

		$query = Db::name('agent_balance')
				->alias('a')
				->join('__CAIPAI_LIST__ c','c.id=a.czid')
                ->join('__TOUZHU_LIST_OK__ tl','tl.id=a.ddid')
				->field('a.*,c.cp_name_old,c.cp_name_new,tl.tz_time')
				->whereTime('a.add_time','BETWEEN',[$begin,$end])
				->where('a.agent',$user)
				->order('a.add_time','DESC')->paginate(10,false,['query'=>request()->param()]);
        
		$this->assign('query',$query);
        
		return $this->fetch('commission_detail');
	}
    //首充报表
    public function firstCharge(){
        $start = input('sDate1') ? strtotime(input('sDate1').' 00:00:00') : time();
        $end = input('sDate2') ? strtotime(input('sDate2').' 23:59:59') : time();
        $time = [$start,$end];
        $user = Db::name('username')->where('nbzh',1)->whereOr('swzh',1)->column('username');
        $first = Db::name('chongzhi_list')
                ->alias('cl')
                ->join('__USERNAME__ us','us.username=cl.username')
                ->where('us.username','not in',$user)
                ->field('cl.username,cl.je,cl.cztime')
                ->where('cl.zt',1)
                ->where('us.reg_time','between',$time)
                ->group('us.username')->order('cl.cztime','ASC')->select();
        
        $this->assign('time',$time);
        $this->assign('first',$first);
        return $this->fetch('first_charge');
    }
    //会员统计
    public function memberTj(){
        $user = input('us_sql');
        $start = input('sDate1') ? strtotime(input('sDate1').' 00:00:00') : strtotime(date('Y-m-d').' 00:00:00');
        $end = input('sDate2') ? strtotime(input('sDate2').' 23:59:59') : strtotime(date('Y-m-d').' 23:59:59');
        $time = [$start,$end];
       
        $maxtx = Db::name('tixian_list')->where('zt',1)->field('username,max(txje) as je,count(id) as count')->group('username')->buildSql();
        $maxcz = Db::name('chongzhi_list')->where('zt',1)->field('username,max(je) as je,count(id) as count')->group('username')->buildSql();
        $user = Db::name('username')
                ->alias('un')
                ->join("{$maxcz} cl",'cl.username=un.username','left')
                ->join("{$maxtx} tl",'tl.username=un.username','left')
                ->join('__US_CLASS__ uc','uc.id=un.us_class')
                ->field('un.id,un.je as je,un.bankren,un.username,un.mobile,un.open,uc.class_name,un.reg_time,un.reg_ip,un.login_time,un.login_ip,un.login_num,cl.count as cz_num,tl.count as tx_num,un.czzje,un.txzje,tl.je as maxtx,cl.je as maxcz')
                ->where('un.swzh',0)->where('un.nbzh',0)
                ->where('un.reg_time','between',$time)
                ->order('un.reg_time','DESC')->paginate(10,false,array('query'=>request()->param()));
        $this->assign('list',$user);
        $this->assign('time',$time);
        return $this->fetch('member_tj');

    }

    function downExcel(){
        $ret = [];
        $title = '会员统计';
        $header="会员账号\t会员姓名\t手机号\t锁定\t会员等级\t注册时间\t最后登录/IP\t登录次数\t存款次数\t存款总额\t最大存款\t取款次数\t取款总额\t最大取款\t盈利\r";
        $start = input('start') ? strtotime(input('start').' 00:00:00') : strtotime(date('Y-m-d').' 00:00:00');
        $end = input('end') ? strtotime(input('end').' 23:59:59') : strtotime(date('Y-m-d').' 23:59:59');
        $time = [$start,$end];

        $maxtx = Db::name('tixian_list')->where('zt',1)->field('username,max(txje) as je,count(id) as count')->group('username')->buildSql();
        $maxcz = Db::name('chongzhi_list')->where('zt',1)->field('username,max(je) as je,count(id) as count')->group('username')->buildSql();
        $user = Db::name('username')
                ->alias('un')
                ->join("{$maxcz} cl",'cl.username=un.username','left')
                ->join("{$maxtx} tl",'tl.username=un.username','left')
                ->join('__US_CLASS__ uc','uc.id=un.us_class')
                ->field('un.id,un.je as je,un.bankren,un.username,un.mobile,un.open,uc.class_name,un.reg_time,un.reg_ip,un.login_time,un.login_ip,un.login_num,cl.count as cz_num,tl.count as tx_num,un.czzje,un.txzje,tl.je as maxtx,cl.je as maxcz,0 as yl')
                ->where('un.swzh',0)->where('un.nbzh',0)
                ->where('un.reg_time','between',$time)
                ->order('un.reg_time','DESC')->select();

        foreach ($user as $key => $v) {
            # code...
            if($v['open']==1){$v['open']='关';}else{$v['open']='开';}
            if(!empty($v['login_ip'])){
                $v['login_time'] = date('Y-m-d H:i:s',$v['login_time']).'/'.$v['login_ip'];
            }else{
                $v['login_time'] = date('Y-m-d H:i:s',$v['reg_time']).'/'.$v['reg_ip'];
            }
            $v['reg_time'] = date('Y-m-d',$v['reg_time']);
            
            $v['yl'] = $v['je']-$v['czzje'];
            $ret[] = [$v['username'],$v['bankren'],$v['mobile'],$v['open'],$v['class_name'],$v['reg_time'],$v['login_time'],$v['login_num'],$v['cz_num'],$v['czzje'],$v['maxcz'],$v['tx_num'],$v['txzje'],$v['maxtx'],sprintf("%.2f",$v['yl'])];
        }
         self::createtab($header,$ret,$title);

    }
    private static function createtab($header,$list,$filename){
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:filename=".$filename.".xls");
        $strexport = $header;
        foreach ($list as $row){
            $length = count($row);
            for( $i=0;$i<$length;$i++ ){
                $a = $row[$i].'';
                if( $i==$length-1 ){
                    $strexport.=$a."\r";
                }else{
                    $strexport.=$a."\t";
                }
            }
        }
        $strexport=iconv('UTF-8',"GB2312//IGNORE",$strexport);
        exit($strexport);
    }

}